package vn.edu.saigontech.ncms.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

public class ExcelUtil {
	public static void makeColumnLabel(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row, int index, String label, int width, HSSFCellStyle style) {
		HSSFCell cell = row.createCell(index, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style);
		cell.setCellValue(new HSSFRichTextString(label));
		sheet.setColumnWidth(index, (short)width);
	}

	public static HSSFCellStyle makeCellStyle(HSSFWorkbook workbook, int halign, int valign, HSSFFont font) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment((short) halign);
		style.setVerticalAlignment((short) valign);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	
	public static HSSFFont makeFont(HSSFWorkbook workbook, String name, int size, boolean isBold) {
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short)size);
		font.setFontName(name);
		if(isBold) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		return font;
	}

	public static void makeBorder(HSSFCellStyle style) {
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setRightBorderColor((short)32767);
		style.setLeftBorderColor((short)32767);
		style.setBottomBorderColor((short)32767);
		style.setTopBorderColor((short)32767);
	}
	
	public static void createBlankCells(int colFrom, int colTo, HSSFRow row, HSSFCellStyle style) {
		for(int i = colFrom; i <= colTo; i++) {
			HSSFCell cell = row.createCell(i, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(""));
			cell.setCellStyle(style);
		}
	}
	
	/**
	 *  get the excel template from the project Folder 
	 */
	public static HSSFWorkbook getTemplate(String fileName) {
		POIFSFileSystem fs;
		HSSFWorkbook wb = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(
					new File(Global.INSTANCE.getParent().getParent() + File.separator
					+ "export" + File.separator + fileName)));
			wb = new HSSFWorkbook(fs, true);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 *  make string value cell
	 */
	public static void makeStringCell(HSSFRow row, int index, String label, Map<String, CellStyle> styles, String styleName) {
		HSSFCell cell = row.createCell(index);
		cell.setCellStyle(styles.get(styleName));
		cell.setCellValue(label);
	}
	
	/**
	 *  make formula cell
	 */
	public static void makeFormulaCell(HSSFRow row, int index, String formula, Map<String, CellStyle> styles, String styleName) {
		HSSFCell cell = row.createCell(index);
		cell.setCellStyle(styles.get(styleName));
		cell.setCellFormula(formula);
	}
	
	/**
	 *  make numeric value cell
	 */
	public static void makeNumericCell(HSSFRow row, int index, int label, Map<String, CellStyle> styles, String styleName) {
		HSSFCell cell = row.createCell(index);
		cell.setCellStyle(styles.get(styleName));
		cell.setCellValue(label);
	}
	
	/**
	 *  make numeric value cell
	 */
	public static void setCellStyle(HSSFRow row, int index, Map<String, CellStyle> styles, String styleName) {
		HSSFCell cell = row.getCell(index);
		cell.setCellStyle(styles.get(styleName));
	}
	
	/** 
	 * set default font for the workbook
	 */
	public static void setDefaultFont(HSSFWorkbook wb, String fontName, short point) {
		for(short i=0; i < wb.getNumberOfFonts(); i++){
			HSSFFont f = wb.getFontAt(i);
			f.setFontName(fontName);
			if (point!=0) f.setFontHeightInPoints(point);
		}
	}
}